<?php

class EvaluationAppraisalsController extends AppController {

    var $name = 'EvaluationAppraisals';

    function index($property_id) {
        $this->layout = "ajax";
        $this->EvaluationAppraisal->recursive = 0;
        $this->set('property_id', $property_id);
        $this->set('avaluos', $this->EvaluationAppraisal->find('all', array('conditions' => array('EvaluationAppraisal.property_id' => $property_id), 'fields' => array('Property.codigo', 'EvaluationAppraisal.area', 'EvaluationAppraisal.calificacion', 'EvaluationAppraisal.numero_catastral', 'EvaluationAppraisal.matricula', 'EvaluationAppraisal.nombre_predio', 'EvaluationAppraisal.id', 'EvaluationAppraisal.vereda', 'EvaluationAppraisal.concepto', 'EvaluationAppraisal.valor', 'Departament.name', 'City.name'), 'order' => array('EvaluationAppraisal.id DESC'))));
    }

    function select() {
        Configure::write('debug', 0);
        $this->set('est', $this->Session->read('estado'));
        $this->set('cities', $this->EvaluationAppraisal->City->find('list', array(
                    'order' => 'name ASC',
                    'conditions' => array('City.departament_id' => $this->data['EvaluationAppraisal']['departament_id'])
                        )
                ));
    }

    function add($property_id) {
        $this->layout = "ajax";
        $this->set('departaments', $this->EvaluationAppraisal->Departament->find('list', array('order' => array('Departament.name ASC'))));
        $this->set('property_id', $property_id);
        $city_id = $this->EvaluationAppraisal->Property->field('city_id', array('id' => $property_id));
        $departament_id = $this->EvaluationAppraisal->Property->City->field('departament_id', array('id' => $city_id));
        $this->set('departamento', $this->EvaluationAppraisal->Property->City->Departament->field('name', array('id' => $departament_id)));
        $this->EvaluationAppraisal->Property->recursive = 0;
        $this->set('predio', $this->EvaluationAppraisal->Property->find('first', array('conditions' => array('Property.id' => $property_id), 'fields' => array('Property.codigo', 'City.name', 'Property.vereda', 'Property.nombre', 'Property.matricula', 'Property.np_departamento', 'Property.np_municipio', 'Property.np_tipo', 'Property.np_sector', 'Property.np_vereda', 'Property.np_predio', 'Property.np_mejora', 'City.id', 'Property.id'))));
        //Traigo datos del ultimo plano topografico del predio 
        App::Import('model', 'TopographicMap');
        $planos = new TopographicMap();
        $planos->recursive = -1;
        $plano = $planos->find('first', array('conditions' => array('TopographicMap.property_id' => $property_id), 'order' => array('TopographicMap.id DESC'), 'fields' => array('TopographicMap.id', 'TopographicMap.area')));
        $this->set('areaPlanoTopografico', $plano['TopographicMap']['area']);
        if (!empty($this->data)) {
            if ($this->EvaluationAppraisal->saveAll($this->data)) {
                $this->Session->setFlash('Registro guardado correctamente');
                $this->redirect(array('Controller' => 'EvaluationAppraisals', 'action' => 'index', $property_id));
            } else {
                $this->Session->setFlash('No se han podido guardar los datos, por favor revise que estos esten completos.');
            }
        }
    }

    function edit($id) {
        $this->layout = "ajax";
        $this->set('departaments', $this->EvaluationAppraisal->Departament->find('list', array('order' => array('Departament.name ASC'))));
        $this->set('cities', $this->EvaluationAppraisal->City->find('list', array('order' => array('City.name ASC'))));
        if (!empty($this->data)) {
            if ($this->EvaluationAppraisal->saveAll($this->data)) {
                $this->Session->setFlash('Registro guardado correctamente');
                $this->redirect(array('Controller' => 'EvaluationAppraisal', 'action' => 'index', $this->data['EvaluationAppraisal']['property_id']));
            } else {
                $this->EvaluationAppraisal->recursive = -1;
                $this->data = $this->EvaluationAppraisal->find('first', array('conditions' => array('EvaluationAppraisal.id' => $id)));

                $property_id = $this->EvaluationAppraisal->field('property_id', array('id' => $id));
                $city_id = $this->EvaluationAppraisal->Property->field('city_id', array('id' => $property_id));
                $departament_id = $this->EvaluationAppraisal->Property->City->field('departament_id', array('id' => $city_id));
                $this->set('departamento', $this->EvaluationAppraisal->Property->City->Departament->field('name', array('id' => $departament_id)));
                $this->EvaluationAppraisal->Property->recursive = 0;
                $this->set('predio', $this->EvaluationAppraisal->Property->find('first', array('conditions' => array('Property.id' => $property_id), 'fields' => array('Property.codigo', 'City.name', 'Property.vereda', 'Property.nombre', 'Property.matricula', 'Property.np_departamento', 'Property.np_municipio', 'Property.np_tipo', 'Property.np_sector', 'Property.np_vereda', 'Property.np_predio', 'Property.np_mejora'))));
                //Traigo datos del ultimo plano topografico del predio 
                App::Import('model', 'TopographicMap');
                $planos = new TopographicMap();
                $planos->recursive = -1;
                $plano = $planos->find('first', array('conditions' => array('TopographicMap.property_id' => $property_id), 'order' => array('TopographicMap.id DESC'), 'fields' => array('TopographicMap.id', 'TopographicMap.area')));
                $this->set('areaPlanoTopografico', $plano['TopographicMap']['area']);

                $this->set('avaluo', $this->EvaluationAppraisal->find('first', array('conditions' => array('EvaluationAppraisal.id' => $id), 'fields' => array('EvaluationAppraisal.area', 'EvaluationAppraisal.calificacion', 'EvaluationAppraisal.numero_catastral', 'EvaluationAppraisal.matricula', 'EvaluationAppraisal.nombre_predio', 'EvaluationAppraisal.id', 'EvaluationAppraisal.vereda', 'EvaluationAppraisal.concepto', 'EvaluationAppraisal.valor'))));
            }
        } else {
            $this->EvaluationAppraisal->recursive = -1;
            $this->data = $this->EvaluationAppraisal->find('first', array('conditions' => array('EvaluationAppraisal.id' => $id)));

            $property_id = $this->EvaluationAppraisal->field('property_id', array('id' => $id));
            $city_id = $this->EvaluationAppraisal->Property->field('city_id', array('id' => $property_id));
            $departament_id = $this->EvaluationAppraisal->Property->City->field('departament_id', array('id' => $city_id));
            $this->set('departamento', $this->EvaluationAppraisal->Property->City->Departament->field('name', array('id' => $departament_id)));
            $this->EvaluationAppraisal->Property->recursive = 0;
            $this->set('predio', $this->EvaluationAppraisal->Property->find('first', array('conditions' => array('Property.id' => $property_id), 'fields' => array('Property.codigo', 'City.name', 'Property.vereda', 'Property.nombre', 'Property.matricula', 'Property.np_departamento', 'Property.np_municipio', 'Property.np_tipo', 'Property.np_sector', 'Property.np_vereda', 'Property.np_predio', 'Property.np_mejora'))));
            //Traigo datos del ultimo plano topografico del predio 
            App::Import('model', 'TopographicMap');
            $planos = new TopographicMap();
            $planos->recursive = -1;
            $plano = $planos->find('first', array('conditions' => array('TopographicMap.property_id' => $property_id), 'order' => array('TopographicMap.id DESC'), 'fields' => array('TopographicMap.id', 'TopographicMap.area')));
            $this->set('areaPlanoTopografico', $plano['TopographicMap']['area']);

            $this->set('avaluo', $this->EvaluationAppraisal->find('first', array('conditions' => array('EvaluationAppraisal.id' => $id), 'fields' => array('EvaluationAppraisal.area', 'EvaluationAppraisal.calificacion', 'EvaluationAppraisal.numero_catastral', 'EvaluationAppraisal.matricula', 'EvaluationAppraisal.nombre_predio', 'EvaluationAppraisal.id', 'EvaluationAppraisal.vereda', 'EvaluationAppraisal.concepto', 'EvaluationAppraisal.valor'))));
        }
    }

    public function report() {
        $this->layout = "csv";
        ini_set('max_execution_time', 600);
        $filename = "Reporte_avaluos" . date("Y-m-d") . ".csv";
        $csv_file = fopen('php://output', 'w');
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        // Las cabeceras del archivo csv  
        $header_row = array("CÓDIGO PROYECTO", "CÓDIGO DEL PREDIO", "NOMBRE DEL PREDIO", "ÁREA TOTAL AVALÚO (ha)", "ÁREA TOTAL UNIDADES (ha)", "VALOR TOTAL ANEXOS ($)", "VALOR TOTAL UNIDADES FISIOGRÁFICAS ($)", "UNIDADES FISIOGRÁFICAS + ANEXOS ($)", "VALOR INICIAL DEL AVALÚO ($)", "CALIFICACIÓN", "CONCEPTO");

        fputcsv($csv_file, $header_row, ';', '"');

        //Selecciono todos los proyectos distintos que tengan un avalúo
        $sql = "SELECT DISTINCT Property.codigo, Property.id, Proyect.codigo
                FROM evaluation_appraisals EvaluationAppraisal
                LEFT JOIN properties Property ON Property.id = EvaluationAppraisal.property_id
                LEFT JOIN proyects Proyect ON Proyect.id = Property.proyect_id
                WHERE 1";
        $proyectos = $this->EvaluationAppraisal->query($sql);

        foreach ($proyectos as $proyecto) {
            $area_unidades_fisiograficas = 0;
            $valor_unidades_fisiograficas = 0;
            $valor_anexos = 0;

            //Selecciono el ultimo avaluo hecho para el proyecto
            $sql = "SELECT Property.nombre, Property.codigo, EvaluationAppraisal.area, EvaluationAppraisal.calificacion, EvaluationAppraisal.id, EvaluationAppraisal.concepto, EvaluationAppraisal.valor
                    FROM evaluation_appraisals EvaluationAppraisal
                    LEFT JOIN properties Property ON Property.id = EvaluationAppraisal.property_id
                    WHERE Property.id = " . $proyecto['Property']['id'] . "
                    ORDER BY EvaluationAppraisal.id DESC
                    LIMIT 1";
            $avaluo = $this->EvaluationAppraisal->query($sql);

            $calificacion = "";

            //selecciono el area y el valor de las unidades fisiográficas asociadas al avalúo
            $sql = "SELECT SUM( EvaluationPhysiographicUnit.area ) area, SUM( EvaluationPhysiographicUnit.valor_unitario * EvaluationPhysiographicUnit.area ) valor
                    FROM evaluation_physiographic_units EvaluationPhysiographicUnit
                    WHERE EvaluationPhysiographicUnit.evaluation_appraisal_id = " . $avaluo[0]['EvaluationAppraisal']['id'] . "
                    GROUP BY EvaluationPhysiographicUnit.evaluation_appraisal_id";
            $unidadesFisiograficas = $this->EvaluationAppraisal->query($sql);

            //selecciono el valor de los anexos asociados al avalúo
            $sql = "SELECT SUM( EvaluationAnnex.valor_unitario * EvaluationAnnex.magnitud ) valor
                    FROM evaluation_annexes EvaluationAnnex
                    WHERE EvaluationAnnex.evaluation_appraisal_id = " . $avaluo[0]['EvaluationAppraisal']['id'] . "
                    GROUP BY EvaluationAnnex.evaluation_appraisal_id";
            $anexos = $this->EvaluationAppraisal->query($sql);


            if (!empty($unidadesFisiograficas)) {
                $area_unidades_fisiograficas = $unidadesFisiograficas[0][0]["area"];
                $valor_unidades_fisiograficas = $unidadesFisiograficas[0][0]["valor"];
            } else {
                //cuando no encuentre unidades fisiograficas asociadas
                $area_unidades_fisiograficas = 0;
                $valor_unidades_fisiograficas = 0;
            }

            if (!empty($anexos)) {
                $valor_anexos = $anexos[0][0]["valor"];
            } else {
                //cuando no encuentre anexos asociados
                $valor_anexos = 0;
            }

            //0=> 'Sin registro',1 => 'Cumple', 2 => 'Subsanable', 3 => 'No cumple'

            switch ($avaluo[0]['EvaluationAppraisal']['calificacion']) {
                case 0:
                    $calificacion = "SIN REGISTRO";
                    break;
                case 1:
                    $calificacion = "CUMPLE";
                    break;
                case 2:
                    $calificacion = "SUBSANABLE";
                    break;
                case 3:
                    $calificacion = "NO CUMPLE";
                    break;

                default:
                    break;
            }

            $fila = array(
                $proyecto['Proyect']['codigo'],
                $avaluo[0]['Property']['codigo'],
                $avaluo[0]['Property']['nombre'],
                number_format($avaluo[0]['EvaluationAppraisal']['area'], 4, ',', '.'),
                number_format($area_unidades_fisiograficas, 4, ',', '.'),
                number_format($valor_anexos, 2, ',', '.'),
                number_format($valor_unidades_fisiograficas, 2, ',', '.'),
                number_format($valor_unidades_fisiograficas + $valor_anexos, 2, ',', '.'),
                number_format($avaluo[0]['EvaluationAppraisal']['valor'], 2, ',', '.'),
                $calificacion,
                $avaluo[0]['EvaluationAppraisal']['concepto']
            );

            fputcsv($csv_file, $fila, ';', '"');
        }

        fclose($csv_file);
    }

//    function delete($id, $property_id) {
//        if ($this->EvaluationAppraisal->delete($id)) {
//            $this->Session->setFlash('Registro borrado correctamente');
//            $this->redirect(array('controller' => 'evaluationAppraisals', 'action' => 'index', $property_id));
//            //$this->redirect(array('controller' => 'Pages', 'action' => 'display'));
//        }
//    }
}

?>